<?php
require_once $ubi['SERVER']['SERVER_ROOT'] . '/dao/dbConnection.php';
require_once $ubi['SERVER']['SERVER_ROOT'] . '/model/item.php';

class ItemDao {
	private $_db;
	
	public function __construct() {
		$this->_db = new DbConnection();
	}
	
	public function addItem($item) {	
		$query = sprintf("CALL sp_AddItem('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%u', '%u', '%s', '%s', '%s', @ItemId);",
					$item->getItem(),
					$item->getUserId(),
					mysql_real_escape_string($item->getDescription()),
					$item->getBeginPrice(),
					$item->getEndPrice(),
					$item->getDateOfPrice(),					
					$item->getUnit(),					
					$item->getProviderId(),
					$item->getCreatedDate(),
					$item->getCategoryList(),
					$item->getResourceList(),
					$item->getAnswerList());		
		
		$result = $this->_db->executeQuery($query);
		
		$result = $this->_db->executeQuery("SELECT @ItemId;");

		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return 0;
		}
		
		if ($row = mysql_fetch_assoc($result)) {			
			$id = $row['@ItemId'];			
		}		
						
		return $id;
	}
	
	public function getItemsByUserId($userID) {
		$query = sprintf("SELECT * FROM items WHERE UserId = '%d'", $userID);
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		
		$items = array();
		while($row = mysql_fetch_assoc($result)) {
			$items[] = $this->getItemFromFetchRow($row);			
		}		
		
		mysql_free_result($result);
		
		return $items;
	}
	
	public function getNumberOfItemsByUserId($userID) {
		$items = array();
		$query = "SELECT * FROM items WHERE UserId = '$userID'";
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		
		$n = mysql_num_rows($result);
		
		mysql_free_result($result);
		
		return $n;
	}
	
	public function getItemsByUserIDFromTo($userID, $from, $offset) {
		$query = sprintf("SELECT * FROM items WHERE UserId = '%u' ORDER BY CreatedDate DESC LIMIT %u, %u", $userID, $from, $offset);
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
			echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		$items = array();
		
		while($row = mysql_fetch_assoc($result)) {
			$item = $this->getItemFromFetchRow($row);			
			$items[] = $item;
		}		
		
		mysql_free_result($result);
						
		return $items;
	}
	
	public function getItem($itemID) {
		$query = sprintf("SELECT * FROM items WHERE ItemId = '%d'", $itemID);
		
		$result = $this->_db->executeQuery($query);

		if (!$result) {
			echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		
		if ($row = mysql_fetch_assoc($result)) {
			$item = $this->getItemFromFetchRow($row);									
		}	
		
		mysql_free_result($result);
		
		return $item;	
	}	
	
	public function addResource($itemID, $resourceID) {
		$query = sprintf("CALL sp_AddResourceToItem('%u', '%s')", $itemID, $resourceID);
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return 0;
		}
		
		$numRows = mysql_affected_rows();		
		
		return $numRows;				
	}
	
	public function setPlusOnePoint($itemID, $userID, $time) {		
		$query = "INSERT INTO UPDATES VALUES ('" . $userID . "', '" . PLUS_ITEM . "', '" . $time . "')";
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return 0;
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;
	}
	
	public function setMinusOnePoint($itemID, $userID, $time) {
		$query = "INSERT INTO Updates VALUES ('" . $userID . "', '" . MINUS_ITEM . "', '" . $time . "')";
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return 0;
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;	
	}
	
	public function updatePlusOnePoint($itemID, $userID, $time) {
		$query = "UPDATES Updates SET UpdateType = '" . PLUS_ITEM
				. "', ModifiedDate = '" . $time 
				. "' WHERE UserId = '" . $userID . "' AND IndicteeId = '" . $itemID . "'";
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return 0;
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;
	}
	
	public function updateMinusOnePoint($itemID, $userID, $time) {
		$query = "UPDATES Updates SET UpdateType = '" . MINUS_ITEM
				. "', ModifiedDate = '" . $time 
				. "' WHERE UserId = '" . $userID . "' AND IndicteeId = '" . $itemID . "'";
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return 0;
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;		
	}
	
	public function increaseTotalRate($itemID) {
		$query = "UPDATES items SET TotalRate = TotalRate + 1";
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return 0;
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;
	}
	
	public function updatePositiveRate($itemID, $value) {
		$query = "UPDATES items SET PositiveRate = PositiveRate + " . $value;
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return 0;
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;	
	}
	
	public function increaseNegativeRate($itemID, $value) {
		$query = "UPDATES items SET NegativeRate = NegativeRate + " . $value;
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return 0;
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;	
	}
	
	public function increasePositiveRate($itemID, $value) {
		$query = "UPDATES items SET PositiveRate = PositiveRate + " . $value;
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return 0;
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;	
	}
	
	public function updateItem($itemModel) {		
		$query = sprintf("CALL sp_UpdateItem('%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%d')", 
					$itemModel->getItem(), $itemModel->getDescription(), $itemModel->getBeginPrice(),
					$itemModel->getEndPrice(), $itemModel->getDateOfPrice(), $itemModel->getUnit(),
					$itemModel->getCategoryList(), $itemModel->getAnswerList(), $itemModel->getItemID());
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return;		    
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;
	}
	
	public function incFrequency($itemID) {
		$query = sprintf("UPDATE items SET Frequency = Frequency + 1 WHERE ItemId = '%d'", $itemID);
		$this->_db->executeQuery($query);
	}

	public function getNumberOfComments($itemID) {
		global $ubi;
		
		$query = sprintf("SELECT COUNT(*) FROM comments WHERE IndicteeId = '%d' AND InteractType = '%d'", 
					$itemID, $ubi['ITEM']['VALUE']);
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return;		    
		}

		$row = mysql_fetch_array($result);			
		$n = $row[0];		
		
		return $n;
	}
	
	public function getNumberOfFavourites($itemID) {
		global $ubi;
		
		$query = sprintf("SELECT COUNT(*) FROM favourites WHERE IndicteeId = '%d' AND InteractType = '%d'", 
					$itemID, $ubi['ITEM']['VALUE']);
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return;		    
		}

		$row = mysql_fetch_array($result);			
		$n = $row[0];		
		
		return $n;
	}
	
	public function getItemMaxFrequency() {
		$query = "SELECT * FROM items WHERE Frequency =(select max(Frequency) from items)";
		$result = $this->_db->executeQuery($query);
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return;		    
		}

		$row = mysql_fetch_array($result);
		
		return $this->getItemFromFetchRow($row); 
	}
	
	public function getNewestItems() {
		global $ubi;
		
		$query = sprintf("SELECT * FROM items LIMIT %d", $ubi['SITE']['NUMBER_NEWEST_POST']);
		$result = $this->_db->executeQuery($query);
		
		$items = array();
		while ($row = mysql_fetch_assoc($result)) {					
			$items[] = $this->getItemFromFetchRow($row);
		}
		return $items;		
	}
	
	public function getNumberOfItem() {
		$query = "SELECT COUNT(*) FROM items";
					
		$result = $this->_db->executeQuery($query);

		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		
		if ($row = mysql_fetch_row($result)) {						
			return $row[0];
		}
	}	
	
//	public function getItemNth($nthItem) {
//		$query = sprintf("SELECT * FROM items LIMIT %d, 1", $nthItem);
//
//		$result = $this->_db->executeQuery($query);
//		
//		if (!$result) {
//			echo 'Could not run query: ' . ' ' . mysql_error();		    
//		}
//		
//		if ($row = mysql_fetch_assoc($result)) {
//			$item = $this->getItemFromFetchRow($row);						
//		}
//		
//		mysql_free_result($result);
//		
//		return $item;
//	}
	
	public function getRandomItems($nItem) {
		$query = sprintf("SELECT * from items ORDER BY RAND() limit %d", $nItem);
		$result = $this->_db->executeQuery($query);
		
		$items = array();
		while ($row = mysql_fetch_assoc($result)) {					
			$items[] = $this->getItemFromFetchRow($row);
		}

		return $items;	
	}
	
	public function getNumberItemPost($userID) {
		$query = sprintf("SELECT COUNT(*) FROM items WHERE UserId = '%d'", $userID);
	
		$result = $this->_db->executeQuery($query);

		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		
		if ($row = mysql_fetch_row($result)) {						
			return $row[0];
		}		
		
	}
	
	public function searchItems($searchText) {
		$query = "SELECT * FROM items WHERE Item LIKE '%" . 
			GeneralFunctions::stripString(trim($searchText)) . "%'";
		
		$result = $this->_db->executeQuery($query);
		if (!$result) {
			echo 'Could not run query: ' . ' ' . mysql_error();
		    return;
		}

		$itemList = array();
		
		while ($row = mysql_fetch_assoc($result)) {					
			$item = $this->getItemFromFetchRow($row);	
			$itemList[] = $item;			
		}
		
		mysql_free_result($result);

		return $itemList;		
	}
	
	public function getItemsOfProvider($providerID) {
		$query = sprintf("SELECT * FROM items WHERE ProviderId = '%d'", $providerID);

		$result = $this->_db->executeQuery($query);
		if (!$result) {
			echo 'Could not run query: ' . ' ' . mysql_error();
		    return;
		}

		$itemList = array();
		
		while ($row = mysql_fetch_assoc($result)) {					
			$item = $this->getItemFromFetchRow($row);	
			$itemList[] = $item;			
		}
		
		mysql_free_result($result);

		return $itemList;
	}
	
	public function searchAdvancedItems($itemName, $beginPrice, $endPrice, $unit) {
		$itemName = '%' . $itemName . '%';
		$unit = '%' . $unit . '%';
		
		$query = sprintf("SELECT * from items WHERE Item LIKE '%s' and Unit LIKE '%s'", $itemName, $unit);
		if (isset($beginPrice) && $beginPrice != '') {
			$query .= sprinf(" AND BeginPrice >= '%d'", $beginPrice);
		}
		if (isset($endPrice) && $endPrice != '') {
			$query .= sprinf(" AND EndPrice <= '%d'", endPrice);
		}
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
			echo 'Could not run query: ' . ' ' . mysql_error();
			return;		    
		}
		
		$items = array();
		while ($row = mysql_fetch_assoc($result)) {
			$items[] = $this->getItemFromFetchRow($row);
		}
		mysql_free_result($result);
		
		return $items;
	}
	
	public function searchItemsByRegion($region) {
		if (!is_numeric($region)) {
			$region = '%' . $region . '%';
			$query = sprintf("SELECT * FROM vwitemsbyregion WHERE City LIKE '%s'", $region);
		} else {
			$query = sprintf("SELECT * FROM vwitemsbyregion WHERE %d IN(Location1, Location2, Location3)", $region);
		}
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return 0;
		}
		
		$items = array();
		while ($row = mysql_fetch_assoc($result)) {			
			$item = $this->getItemFromFetchRow($row);			
			$items[] = $item;
		}
		return $items;
	}
	
	private function getItemFromFetchRow($row) {
		$item = new ItemModel();		
			
		$item->setAccessedDate($row['AccessedDate']);
		$item->setAnswerList($row['AnswerList']);
		$item->setBeginPrice($row['BeginPrice']);
		$item->setCategoryList($row['CategoryList']);
		$item->setCreatedDate($row['CreatedDate']);
		$item->setDateOfPrice($row['DateOfPrice']);
		$item->setDescription($row['Description']);
		$item->setEndPrice($row['EndPrice']);
		$item->setFrequency($row['Frequency']);
		$item->setItem($row['Item']);
		$item->setItemId($row['ItemId']);
		$item->setModifiedDate($row['ModifiedDate']);
		$item->setNegativeRate($row['NegativeRate']);
		$item->setPositiveRate($row['PositiveRate']);
		$item->setProviderId($row['ProviderId']);
		$item->setResourceList($row['ResourceList']);
		$item->setStatus($row['Status']);
		$item->setTotalRate($row['TotalRate']);
		$item->setUnit($row['Unit']);
		$item->setUserId($row['UserId']);
		return $item;
	}	
}
?>